import pymssql;
import json;
import pymysql;

with open("cfg.json","r") as file:    
    json_data = json.load(file)
SQLSERVER_HOST = json_data["SQLSERVER_HOST"]
print(SQLSERVER_HOST)
SQLSERVER_USER = json_data["SQLSERVER_USER"]
print(SQLSERVER_USER)
SQLSERVER_PASSWORD = json_data["SQLSERVER_PASSWORD"]
print("SQLSERVER_PASSWORD:" + SQLSERVER_PASSWORD)
SQLSERVER_DB = json_data["SQLSERVER_DB"]
print("SQLSERVER_DB:" + SQLSERVER_DB)
SQLSERVER_TABLE = json_data["SQLSERVER_TABLE"]
print("SQLSERVER_TABLE:" + SQLSERVER_TABLE)
IDCOLUMN = json_data["IDCOLUMN"]
print("IDCOLUMN:"+IDCOLUMN)
MYSQL_HOST = json_data["MYSQL_HOST"]
print("MYSQL_HOST:" + MYSQL_HOST)
MYSQL_USER = json_data["MYSQL_USER"]
print("MYSQL_USER:" + MYSQL_USER)
MYSQL_PASSWORD = json_data["MYSQL_PASSWORD"]
print("MYSQL_PASSWORD:" + MYSQL_PASSWORD)
MYSQL_DB = json_data["MYSQL_DB"]
print("MYSQL_DB:" + MYSQL_DB)
COLUMNS = json_data["COLUMNS"]
mysql_conn = pymysql.connect(MYSQL_HOST,MYSQL_USER,MYSQL_PASSWORD,MYSQL_DB)
mysql_cursor1 = mysql_conn.cursor()
LASTID = str(0);
mysql_cursor1.execute("SELECT "+ IDCOLUMN + " FROM " +TABLE +" ORDER BY "+ IDCOLUMN +" DESC LIMIT 1")
LASTID_ROW = mysql_cursor1.fetchone()
if LASTID_ROW:
    LASTID = str(LASTID_ROW[0])
QUERY = "SELECT "+ COLUMNS.strip(',')+" FROM "+ TABLE + " WITH(NOLOCK) WHERE " + IDCOLUMN + ">" + LASTID +" ORDER BY "+ IDCOLUMN
print("SQLSERVER_QUERY:" + QUERY)
sqlserver_conn = pymssql.connect(SQLSERVER_HOST,SQLSERVER_USER,SQLSERVER_PASSWORD,SQLSERVER_DB)
sqlserver_cursor = sqlserver_conn.cursor()
sqlserver_cursor.execute(QUERY)
row = sqlserver_cursor.fetchone()
columns_arr = COLUMNS.strip(',').split(',')
InsertSQL = "INSERT INTO "+TABLE + "("+ COLUMNS.strip(',') + ") VALUES(";
for item in columns_arr:
    InsertSQL += "%s,"
InsertSQL = InsertSQL.strip(',') +")";
print(InsertSQL)
COLUMN_COUNT= len(columns_arr)
mysql_cursor2 = mysql_conn.cursor()
while row:    
    data = []
    i = 0
    while(i < COLUMN_COUNT):
        data.append(row[i])            
        i += 1
    mysql_cursor2.execute(InsertSQL,data)
    mysql_conn.commit()
    print(str(row[0]) + " 成功插入一条数据")
    row = sqlserver_cursor.fetchone()
    
sqlserver_conn.close()
mysql_conn.close()